<?php


declare(strict_types=1);

namespace App\Controller\Abell;

use App\Controller\AbstractController;
use App\Utils\ApiUtils;
use App\Utils\Excel;
use Hyperf\HttpServer\Contract\RequestInterface;
use Hyperf\HttpServer\Annotation\Controller;
use Hyperf\HttpServer\Annotation\RequestMapping;
// excel
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// mongodb
use Hyperf\Mongodb\MongoDb;
use Hyperf\Di\Annotation\Inject;
use App\Utils\Excel as ExcelUtils;
use App\Dao\LotteryPrizeDao;

/**
 * @Controller()
 */
class ExcelController extends AbstractController{

    /**
     * @Inject()
     * @var MongoDb
     */
    protected $mongoDbClient;

    private $db_name = 'pc_wxuser';

    /** 写入excel测试数据 使用已存在路径测试ok
     * @param bool force
     * @return array
     * Abell 2020/5/20
     * @RequestMapping(path="/abell/excel/hello", methods="get,post")
     */
    public function import(RequestInterface $request){
        $excel_file = $request->file('excel');
        $excel_read = make(Excel::class)->excel($excel_file);
        return $excel_read;
    }

    /** 导出excel文档  ==> ok
     * @param array $heads 标题头数组
     * @param array $data 表格内容数组
     * @return str 文档下载链接url
     * Abell 2020/5/19
     * @RequestMapping(path="/abell/excel/aeexcel", methods="get")
     */
    public function export(RequestInterface $request){
//        $heads = [
//            'titile' => '微信用户表',
//            'keys_array' => ['openid' => 'openid' , 'nickname' => '呢称' ,'avatarurl' => '头像' ,'gender' => '性别','city' => '市区']
//        ];
        $heads = ['openid' => 'openid' , 'nickname' => '呢称' ,'avatarurl' => '头像' ,'gender' => '性别','city' => '市区'];
        $data = [
            ['openid' => 'openid1' , 'nickname' => '呢称1' ,'avatarurl' => '头像' ,'gender' => '性别','city' => '市区'],
            ['openid' => 'openid2' , 'nickname' => '呢称2' ,'avatarurl' => '头像' ,'gender' => '性别','city' => '市区'],
            ['openid' => 'openid3' , 'nickname' => '呢称3' ,'avatarurl' => '头像' ,'gender' => '性别','city' => '市区'],
        ];

        return  make(LotteryPrizeDao::class)->outputLog($data,$heads);

    }

    /** 上传excel文件读取==ok
     * @param bool force
     * @return array
     * Abell 2020/5/19
     * @RequestMapping(path="/abell/excel/readexcelonly", methods="get")
     */
    public function readexcelonly(RequestInterface $request)
    {
        $file_path = $request->file('file');
        // 上传文件
        $upload_file = make(Upload::class)->upload_file($file_path);
        $file_path = BASE_PATH.$upload_file;
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $spread_sheet = $reader->load($file_path);
        $work_sheet = $spread_sheet->getActiveSheet();
        var_dump('总行数'); // 102
        $highest_row = $work_sheet->getHighestRow();   // 总行数
        var_dump($highest_row);
        $highest_column = $work_sheet->getHighestColumn(); // 总列数
        var_dump('总列数'); // F
        var_dump($highest_column);
        $highest_column_index = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highest_column);
        $lines = $highest_row - 2;
        if($lines <= 0) return  0;
        // 处理标题
        $abell_title = $work_sheet->getCellByColumnAndRow(1,1)->getValue();
        // 处理表头
        $head_array = array();
        for($i=1;$i<=$highest_column_index;$i++){
            array_push($head_array,$work_sheet->getCellByColumnAndRow($i,2)->getValue());
        }
        $content_array = array();
        for($j=3;$j<=$highest_row;$j++){
            $box_array = array();
            for($i=1;$i<=$highest_column_index;$i++){
                array_push($box_array,$work_sheet->getCellByColumnAndRow($i,$j)->getValue());
            }
            array_push($content_array,$box_array);
        }
        return [
            '总行数' => $highest_row , '总列数' => $highest_column ,'title' => $abell_title,
            'head_array' => $head_array,'content_array' => $content_array
        ];
    }

    /** 直接读取已知目录的xlsx文件  ==OK
     * @param bool force
     * @return array
     * Abell 2020/5/19
     * @RequestMapping(path="/abell/excel/readexcel", methods="get")
     */
    public function readexcel(RequestInterface $request){
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $file_path = BASE_PATH . $request->input('file',  '/upload_file/excel/20200519/5ec3c62f7e2a9.xlsx');
        var_dump($file_path);
        $spread_sheet = $reader->load($file_path);
        $work_sheet = $spread_sheet->getActiveSheet();
        var_dump('总行数'); // 102
        $highest_row = $work_sheet->getHighestRow();   // 总行数
        var_dump($highest_row);
        $highest_column = $work_sheet->getHighestColumn(); // 总列数
        var_dump('总列数'); // F
        var_dump($highest_column);
        $highest_column_index = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highest_column);
        $lines = $highest_row - 2;
        if($lines <= 0) return  0;
        // 处理标题
        $abell_title = $work_sheet->getCellByColumnAndRow(1,1)->getValue();
        // 处理表头
        $head_array = array();
        for($i=1;$i<=$highest_column_index;$i++){
            array_push($head_array,$work_sheet->getCellByColumnAndRow($i,2)->getValue());
        }
        $content_array = array();
        for($j=3;$j<=$highest_row;$j++){
            $box_array = array();
            for($i=1;$i<=$highest_column_index;$i++){
                array_push($box_array,$work_sheet->getCellByColumnAndRow($i,$j)->getValue());
            }
            array_push($content_array,$box_array);
        }
        return [
            '总行数' => $highest_row , '总列数' => $highest_column ,'title' => $abell_title,
            'head_array' => $head_array,'content_array' => $content_array
        ];
    }

    /** 查询mango数据
     * @param bool force
     * @return array
     * Abell 2020/5/16
     * @RequestMapping(path="/abell/excel/wxexcel", methods="get")
     */
    public function wxexcel(RequestInterface $request){
        $start_time = time();
        $titles_array = ['openid' , '呢称' ,'头像' ,'性别','市区','手机号'];
        $key_array = ['openid','nickname','avatarurl','gender','city','mobile'];
        $data_array = $this->mongoDbClient->fetchPagination($this->db_name,(int)$request->input('pageSize','100'),(int)$request->input('page',1));
        $alpha_str = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

        $spreadSheet = new Spreadsheet();
        $worksheet = $spreadSheet->getActiveSheet();
        // 所有的行居中
        $alpha_str_array = str_split($alpha_str);
        for($i=0;$i<count($titles_array);$i++){
            $spreadSheet->getActiveSheet()->getColumnDimension($alpha_str_array[$i])->setAutoSize(true);
        }
        $work_title = '微信用户表';
        $worksheet->setTitle('微信用户表');
        // 标题设置
        $worksheet->setCellValueByColumnAndRow(1,1,$work_title);
        // 表头设置
        $key_num = 1;
        foreach ($titles_array as $key) {
            $worksheet->setCellValueByColumnAndRow($key_num,2,$key);$key_num++;
        }
        //合并单元格 标题头合并
        $work_length_alpha = substr($alpha_str,count($titles_array)-1,1);
        $worksheet->mergeCells('A1:'.$work_length_alpha.'1');
        $styleArray = ['font' => ['bold' => true],
            'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,],];
        //设置单元格样式
//        $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);
//        $worksheet->getStyle('A1:'.$work_length_alpha.'1')->applyFromArray($styleArray)->getFont()->setSize(14);

        $data_array = $data_array['list'];
        for($i =0;$i<count($data_array);$i++){
            $j = $i+3; $key_value_num = 1;
            foreach ($key_array as $key_value) {
                $worksheet->setCellValueByColumnAndRow($key_value_num,$j,$data_array[$i][$key_value]);$key_value_num++;
            }
        }
        $styleArrayBody = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '666666'],],],
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],];
        $total_rows = count($data_array) + 2;
        //添加所有边框/居中
        $worksheet->getStyle('A1:E'.$total_rows)->applyFromArray($styleArrayBody);
        $filePath = substr(config('web.img_path'),0,13).'excel/'.date("Ymd") . "/" ;
        var_dump($filePath);
        !is_dir(BASE_PATH.$filePath)&&@mkdir(BASE_PATH.$filePath,0777,true);
        $filename = $filePath.uniqid().'.xlsx';
        $writer = new Xlsx($spreadSheet);
        $writer->save(BASE_PATH.$filename);
        $file_url = config('web.web_host').substr($filename,12);
        $end_time = time();
        var_dump(date('Ymd H:i:s',$start_time));
        var_dump(date('Ymd H:i:s',$end_time));
        return $this->response->json(ApiUtils::send(0,'ok',$file_url,1));
    }

    /** 查询mango数据
     * @param bool force
     * @return array
     * Abell 2020/5/16
     * @RequestMapping(path="/abell/excel/wxuser", methods="get")
     */
    public function wxuser(){
//        $result = $this->mongoDbClient->fetchAll($this->db_name);
        $list = $this->mongoDbClient->fetchPagination($this->db_name,3,0);
        $list_data = $list['list'];
        // 'openid' => 'openid' , 'nickname' => '呢称' ,'avatarurl' => '头像' ,'gender' => '性别','province' => '省份'
        $keys_array = ['openid' , 'nickname' ,'avatarurl' ,'gender','province'];
        //
        for($i=0;$i<count($list_data);$i++){
            for($j=0;$j<count($keys_array);$j++){
                var_dump($list_data[$i][$keys_array[$j]]);
            }
        }
        return $this->response->json(ApiUtils::send(0,'ok',$list,1));
    }

    /**
     * mongodb 列表数据展示
     * Abell 2020/5/15
     * @RequestMapping(path="/abell/excel/list", methods="get")
     */
    public function list(RequestInterface $request){
//        $result = $this->mongoDbClient->fetchAll($this->db_name);
        $count = $this->mongoDbClient->count($this->db_name);
        var_dump($count);
        return $this->response->json(ApiUtils::send(0,'ok',$count,1));
    }
}